The MERGE statement is a data manipulation language (DML) statement that may be employed to update a target using data from a source. Each of the target and the source may be a table, for example. Rows in the target that match corresponding rows in the source can be deleted or updated as specified in the MERGE statement. Rows that do not exist in the target can be inserted. Thus, MERGE allows performing a mix of inserts, updates, and deletes in a single statement.
Such a statement introduces new challenges compared to legacy DML statements, where the kind of action to be performed is hard-coded and known at compile time. To effect a MERGE, it must first be determined whether or not a corresponding row exists in the target. If not, then the row from the source may be inserted into the target. If the row exists in the target, then it must be determined whether to update the target row, delete it, or leave it unchanged, based on the source. Sometimes, such queries are nondeterministic, such as where multiple rows in the source correspond to only a single row in the target. Also, the actions to be taken may depend on the order in which the rows are processed.
There is an ongoing desire for more efficient query processing of MERGE statements.